﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace Marmat.Data
{
    [Serializable]
    public class Access : BaseConnection
    {



        /* TODO ERROR: Skipped RegionDirectiveTrivia */
        public Access(string ConnectionString)
        {
            this.ConnectionString = ConnectionString;
        }
        /* TODO ERROR: Skipped EndRegionDirectiveTrivia */
        /* TODO ERROR: Skipped RegionDirectiveTrivia */
        /// <summary>
        /// 返回或设置数据源的名称
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public string Name
        {
            get
            {
                return this.m_Name;
            }

            set
            {
                this.m_Name = value;
            }
        }

        internal string m_Name;

        /// <summary>
        /// 返回或设置数据源的链接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if (this._Connection is object)
                {
                    return this._Connection.ConnectionString;
                }
                else
                {
                    return this.m_ConnectionString;
                }
            }

            set
            {
                if (this._Connection is object)
                {
                    this._Connection.ConnectionString = value;
                }
                else
                {
                    this.m_ConnectionString = value;
                }
            }
        }

        private string m_ConnectionString;

        /// <summary>
        /// 获取连接的当前状态
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public ConnectionState State
        {
            get
            {
                return this.Connection.State;
            }
        }

        /// <summary>
        /// 返回数据源的System.Data.OleDb.OleDbConnection
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        private OleDbConnection Connection
        {
            get
            {
                if (this._Connection is null)
                    this._Connection = new OleDbConnection(this.ConnectionString);
                return this._Connection;
            }
        }

        private OleDbConnection _Connection;

        public SourceType SourceType => SourceType.Access;

        private ConnectionVersionEnum _Connectionversion;
        /// <summary>
        /// 返回数据源的版本号
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public ConnectionVersionEnum ConnectionVersion
        {
            get
            {
                if (this._Connectionversion == default(int))
                {
                    var cmd = new SQLCommand(this.Name, "select @@version");
                    string version = Convert.ToString(cmd.ExecuteScalar());
                    if (version.StartsWith("Microsoft SQL Server  2000"))
                    {
                        this._Connectionversion = ConnectionVersionEnum.MsSql2000;
                    }
                    else if (version.StartsWith("Microsoft SQL Server  2005"))
                    {
                        this._Connectionversion = ConnectionVersionEnum.MsSql2005;
                    }
                    else if (version.StartsWith("Microsoft SQL Server 2008"))
                    {
                        this._Connectionversion = ConnectionVersionEnum.MsSql2008;
                    }
                    else
                    {
                        this._Connectionversion = ConnectionVersionEnum.None;
                    }
                }

                return this._Connectionversion;
            }
        }
        /// <summary>
        /// 使用所指定的属性设置打开数据库连接
        /// </summary>
        public void Open()
        {
            if (!string.IsNullOrEmpty(this.ConnectionString))
            {
                if (this.State == ConnectionState.Closed)
                {
                    this.Connection.Open();
                }
            }
        }

        /// <summary>
        /// 使用所指定的属性设置关闭数据库连接
        /// </summary>
        /// <remarks></remarks>
        public void Close()
        {
            if (!string.IsNullOrEmpty(this.ConnectionString))
            {
                if (this.State != ConnectionState.Closed)
                {
                    this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 获取SQL Server服务器时间
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public DateTime GetDate()
        {
            DateTime obj = default;
            switch (this.ConnectionVersion)
            {
                case ConnectionVersionEnum.MsSql2000:
                case ConnectionVersionEnum.MsSql2005:
                case ConnectionVersionEnum.MsSql2008:
                    {
                        var cmd = new SQLCommand(this.Name, "Select GetDate()");
                        obj = Convert.ToDateTime(cmd.ExecuteScalar());
                        break;
                    }
            }

            if (obj == default)
            {
                obj = DateTime.Now;
            }

            return obj;
        }

        /// <summary>
        /// 返回此 System.Data.SqlClient.SqlConnection 的数据源的架构信息。
        /// </summary>
        /// <param name="collectionName">指定要返回的架构的名称(仅对MsSql有效)。</param>
        /// <param name="restrictionValues">请求的架构的一组限制值(仅对MsSql有效)。</param>
        /// <returns></returns>
        /// <remarks></remarks>
        private DataTable GetSchema(string collectionName, string[] restrictionValues)
        {
            DataTable dt;
            try
            {
                this.Open();
                dt = this.Connection.GetSchema(collectionName, restrictionValues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }

            return dt;
        }

        /// <summary>
        /// 返回数据源所有数据表的集合
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public List<string> GetTableNames()
        {
            try
            {
                this.Open();
                DataTable dt;
                var list = new List<string>();
                dt = this.Connection.GetSchema("TABLES", new[] { null, null, null, "BASE TABLE" });
                foreach (DataRow dr in dt.Rows)
                    list.Add(Convert.ToString(dr["Table_NAME"]));
                this.Close();
                return list;
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 返回数据源所有视图的集合
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public List<string> GetViewNames()
        {
            try
            {
                this.Open();
                DataTable dt;
                var list = new List<string>();
                dt = this.Connection.GetSchema("TABLES", new[] { null, null, null, "VIEW" });
                foreach (DataRow dr in dt.Rows)
                    list.Add(Convert.ToString(dr["Table_NAME"]));
                this.Close();
                return list;
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 返回数据源所有存储过程的集合
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public List<string> GetProceduresNames()
        {
            try
            {
                this.Open();
                DataTable dt;
                var list = new List<string>();
                dt = this.Connection.GetSchema("Procedures");
                foreach (DataRow dr in dt.Rows)
                    list.Add(Convert.ToString(dr["ROUTINE_NAME"]));
                this.Close();
                return list;
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 返回数据源指定表的字段结构
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public DataTable GetColumns(string TableName)
        {
            return this.GetColumns(TableName, null);
        }

        /// <summary>
        /// 返回数据源指定表的字段结构
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public DataTable GetColumns(string TableName, params string[] ColNames)
        {
            try
            {
                this.Open();
                DataTable dt;
                var list = new List<string>();
                dt = this.Connection.GetSchema("Columns", new[] { null, null, TableName });
                this.Close();
                if (ColNames.Length > 0)
                {
                    return dt.DefaultView.ToTable(false, ColNames);
                }

                return dt;
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }
        }

        public object ExecuteScalar(ConnectionExecuteOptions options)
        {
            using var sqlCommand = new OleDbCommand(options.CommandText, this.Connection);
            sqlCommand.CommandTimeout = options.CommandTimeOut;
            foreach (string Item in options.Parameters.Keys)
                sqlCommand.Parameters.AddWithValue(Item, options.Parameters[Item]);
            return sqlCommand.ExecuteScalar();
        }

        public int ExecuteNonQuery(ConnectionExecuteOptions options)
        {
            using var sqlCommand = new OleDbCommand(options.CommandText, this.Connection);
            sqlCommand.CommandTimeout = options.CommandTimeOut;
            foreach (string Item in options.Parameters.Keys)
                sqlCommand.Parameters.AddWithValue(Item, options.Parameters[Item]);
            return sqlCommand.ExecuteNonQuery();
        }

        public DataTable ExecuteReader(ConnectionExecuteOptions options)
        {
            DataTable dt = null;
            using (var sqlDataAdapter = new OleDbDataAdapter(options.CommandText, this.Connection))
            {
                sqlDataAdapter.SelectCommand.CommandTimeout = options.CommandTimeOut;
                foreach (string Item in options.Parameters.Keys)
                    sqlDataAdapter.SelectCommand.Parameters.AddWithValue(Item, options.Parameters[Item]);
                sqlDataAdapter.Fill(dt);
            }
            return dt;
        }
    }
}